const { table } = require('console');
var express = require('express');
var router = express.Router();
var sqlite3 = require('sqlite3').verbose();
var path = require('path');
/* GET users listing. */
router.get('/', function (req, res, next) {
    var db = new sqlite3.Database(path.join(__dirname, "../db/Delphi.s3db"));
    try {
        if(req.query.question=='2_1'){
            sqlStr = 'select n21,n14,n7 from answer2_1'
            db.all(sqlStr,function(err,rows){
                if(rows.length!=0){
                    var table = {"list":[0,0,0]};
                    var sum1=0,sum2=0,sum3=0
                    console.log(rows)
                    for(i=0;i<rows.length;i++){
                        sum1+=parseFloat(rows[i]['n21']) 
                        sum2+=parseFloat(rows[i]['n14'])
                        sum3+=parseFloat(rows[i]['n7'])
                    }
                    var avg1 = (sum1/rows.length).toFixed(2)
                    var avg2 = (sum2/rows.length).toFixed(2)
                    var avg3 = (sum3/rows.length).toFixed(2)
                    var x1=0,x2=0,x3=0
                    for(i=0;i<rows.length;i++){
                        x1+=(parseFloat(rows[i]['n21']) -avg1)*(parseFloat(rows[i]['n21']) -avg1)
                        x2+=(parseFloat(rows[i]['n14']) -avg2)*(parseFloat(rows[i]['n14']) -avg2)
                        x3+=(parseFloat(rows[i]['n7']) -avg3)*(parseFloat(rows[i]['n7']) -avg3)
                    }
                    table.list[0] = Math.sqrt(x1/rows.length).toFixed(2)
                    table.list[1] = Math.sqrt(x2/rows.length).toFixed(2)
                    table.list[2] = Math.sqrt(x3/rows.length).toFixed(2)
                    console.log(table)
                    res.status(200).json(table);
                }
            })
        }
        else if(req.query.question=='2_2'){
            sqlStr='select g147,g77,g07,gno from answer2_2'
            db.all(sqlStr,function(err,rows){
                if(rows.length!=0){
                    var table = {"list":[0,0,0,0]};
                    var sum1=0,sum2=0,sum3=0,sum4=0
                    console.log(rows)
                    for(i=0;i<rows.length;i++){
                        sum1+=parseFloat(rows[i]['g147']) 
                        sum2+=parseFloat(rows[i]['g77'])
                        sum3+=parseFloat(rows[i]['g07'])
                        sum4+=parseFloat(rows[i]['gno'])
                    }
                    var avg1 = (sum1/rows.length).toFixed(2)
                    var avg2 = (sum2/rows.length).toFixed(2)
                    var avg3 = (sum3/rows.length).toFixed(2)
                    var avg4 = (sum4/rows.length).toFixed(2)
                    var x1=0,x2=0,x3=0,x4=0
                    for(i=0;i<rows.length;i++){
                        x1+=(parseFloat(rows[i]['g147']) -avg1)*(parseFloat(rows[i]['g147']) -avg1)
                        x2+=(parseFloat(rows[i]['g77']) -avg2)*(parseFloat(rows[i]['g77']) -avg2)
                        x3+=(parseFloat(rows[i]['g07']) -avg3)*(parseFloat(rows[i]['g07']) -avg3)
                        x4+=(parseFloat(rows[i]['gno']) -avg4)*(parseFloat(rows[i]['gno']) -avg4)
                    }
                    table.list[0] = Math.sqrt(x1/rows.length).toFixed(2)
                    table.list[1] = Math.sqrt(x2/rows.length).toFixed(2)
                    table.list[2] = Math.sqrt(x3/rows.length).toFixed(2)
                    table.list[3] = Math.sqrt(x4/rows.length).toFixed(2)
                    console.log(table)
                    res.status(200).json(table);
                }
            })
        }
        else{
            sqlStr='select byes,b10,b11,b12,bno from answer3'
            db.all(sqlStr,function(err,rows){
                if(rows.length!=0){
                    var table = {"list":[0,0,0,0,0]};
                    var sum1=0,sum2=0,sum3=0,sum4=0,sum5 = 0
                    console.log(rows)
                    for(i=0;i<rows.length;i++){
                        sum1+=parseFloat(rows[i]['byes']) 
                        sum2+=parseFloat(rows[i]['b10'])
                        sum3+=parseFloat(rows[i]['b11'])
                        sum4+=parseFloat(rows[i]['b12'])
                        sum5+=parseFloat(rows[i]['bno'])
                    }
                    var avg1 = (sum1/rows.length).toFixed(2)
                    var avg2 = (sum2/rows.length).toFixed(2)
                    var avg3 = (sum3/rows.length).toFixed(2)
                    var avg4 = (sum4/rows.length).toFixed(2)
                    var avg5 = (sum5/rows.length).toFixed(2)
                    var x1=0,x2=0,x3=0,x4=0,x5=0
                    for(i=0;i<rows.length;i++){
                        x1+=(parseFloat(rows[i]['byes']) -avg1)*(parseFloat(rows[i]['byes']) -avg1)
                        x2+=(parseFloat(rows[i]['b10']) -avg2)*(parseFloat(rows[i]['b10']) -avg2)
                        x3+=(parseFloat(rows[i]['b11']) -avg3)*(parseFloat(rows[i]['b11']) -avg3)
                        x4+=(parseFloat(rows[i]['b12']) -avg4)*(parseFloat(rows[i]['b12']) -avg4)
                        x5+=(parseFloat(rows[i]['bno']) -avg5)*(parseFloat(rows[i]['bno']) -avg5)
                    }
                    table.list[0] = Math.sqrt(x1/rows.length).toFixed(2)
                    table.list[1] = Math.sqrt(x2/rows.length).toFixed(2)
                    table.list[2] = Math.sqrt(x3/rows.length).toFixed(2)
                    table.list[3] = Math.sqrt(x4/rows.length).toFixed(2)
                    table.list[4] = Math.sqrt(x5/rows.length).toFixed(2)
                    console.log(table)
                    res.status(200).json(table);
                }
            })
            
        }
    } catch (ex) {
        console.log(ex);
    }
});
module.exports = router;